﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;
using ExcelTools = Microsoft.Office.Tools.Excel;
using ColumnsInformation;
using SqlTypeToFramerworkType;
using Cfg;

namespace ListObjectMaker
{
    public class MyListObject
    {
        public ExcelTools.Worksheet _worksheet = null;
        DataTable _dataTable = null;        
        string _selCellAddress;
        Excel.Range _rngTabInsert;
        public ExcelTools.ListObject _listObject = null;
        static int cnt = 0;
        public string Name { get; set; }

        public MyListObject(ExcelTools.Worksheet worksheet, DataTable dataTable, string selCellAddress, SqlConnectionConfig config, int startRow, int endRow, int num)
        {
            try
            {
                _worksheet = worksheet;
                _selCellAddress = selCellAddress;
                int row, col;
                GetRowAndColumn(_selCellAddress, out row, out col);
                _rngTabInsert = _worksheet.Cells[row, col];
                
                _dataTable = dataTable;
                Name = String.Format("{0}.Table#{1}", worksheet.Name, num); //String.Format("[{0}].[{1}].[{2}][{3}-{4}]", config.Database, config.Schema, config.Table, startRow, endRow);
                _listObject = _worksheet.Controls.AddListObject(_rngTabInsert, Name);

            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw;
            }

        }
        public static void GetRowAndColumn(string address, out int row, out int col)
        {
            string str = "";
            int i, len = address.Length;
            for (i = 1; i < len; i++)
                if (address[i] == 'C')
                    break;
                else
                    str += address[i];
            row = Convert.ToInt32(str);
            i++;
            str = "";
            for (;i < len; i++)
                str += address[i];
            col = Convert.ToInt32(str);
        }
        public void GenerateExcelTable()
        {
            try
            {
                _listObject.AutoSetDataBoundColumnHeaders = true;
                _listObject.SetDataBinding(_dataTable);                
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                throw ex;
            }           
        }
        public void SetDataBinding(DataTable dataTable)
        {
            _listObject.SetDataBinding(dataTable);
        }

        public void Delete()
        {
            _listObject.Delete();
            _listObject = null;
        }
        public void AutoFit()
        {
            foreach (Excel.ListColumn column in _listObject.ListColumns)
            {
                column.Range.EntireColumn.AutoFit();
            }
            foreach (Excel.ListRow row in _listObject.ListRows)
            {
                row.Range.EntireRow.AutoFit();
            }
        }

        public void SetListObject(ExcelTools.ListObject listObject)
        {
            _listObject = listObject;
        }
    }
}
